package com.cmpe.av;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.util.ArrayList;


import com.cmpe.av.model.Report;
import com.cmpe.av.model.User;


public class MySQLAccess {
  private Connection connect = null;
  private Statement statement = null;
  private ResultSet resultSet = null;
  private final String USERNAME = "project6";
  private final String PASSWORD = "17Souf";
  private final String URL = "jdbc:mysql://titan.cmpe.boun.edu.tr:3306/database6";
  public MySQLAccess() throws Exception{
	  try {
	      // This will load the MySQL driver, each DB has its own driver
	      Class.forName("com.mysql.jdbc.Driver");
	      // Setup the connection with the DB
	      connect = DriverManager
	          .getConnection(URL,USERNAME,PASSWORD);  
	   
	      
	    } catch (Exception e) {
	      throw e;
	    } 
  }
  public ArrayList<Report> getViolations(){

    return null;

  }
//  public String getDevelopers() throws SQLException{
//	  statement = connect.createStatement();
//	  resultSet = statement.executeQuery("SELECT * from developers");
//	  String result="asdf";
//	  while(resultSet.next()){
//		  result+=resultSet.getString(1);
//		  result+=" " +resultSet.getString(2);
//		  result+= "\n";
//	  }
//	  statement.close();
//	  resultSet.close();
//	  return result;
//  }
  
  public boolean isInDb(String Username) throws SQLException{
	  boolean inDb = false;
	  PreparedStatement statement = null;
	  String sql = "SELECT * FROM Users WHERE Username = ?";
	  statement = connect.prepareStatement(sql);
	  statement.setString(1, Username);
	  resultSet = statement.executeQuery();
	  if(resultSet.next()) {
			inDb = true;
		}
	  
	  statement.close();
	  resultSet.close();
	  return inDb;
  }
  public User getUser(String user, String pass) throws SQLException{
	  
	  PreparedStatement statement = null;
	  String sql = "SELECT * FROM Users WHERE Username = ? AND password = ?";
	  statement = connect.prepareStatement(sql);
	  statement.setString(1,user);
	  statement.setString(2, pass);
	  resultSet = statement.executeQuery();
	  User user1 = null;
	  if(resultSet.next()){
		  user1 = new User();
		  user1.setAll(resultSet.getInt(8), resultSet.getString(2),resultSet.getString(3), resultSet.getString(1), resultSet.getTimestamp(4), resultSet.getInt(5), resultSet.getString(6),
				  resultSet.getString(7),resultSet.getInt(9), resultSet.getInt(10), resultSet.getInt(11), resultSet.getInt(12));
		  
	  }
	  
	  statement.close();
	  resultSet.close();	  
	  return user1;
  }
  
  public void addUser(String user, String email, String password) throws SQLException{
	  statement = connect.createStatement();
	  statement.executeUpdate("INSERT INTO Users(Username,IsAdmin,email,password) " + "VALUES ('"+user+"',"+ 0 +",'"+email+"','"+password+"')");
	  
  }
  
  public void close() throws SQLException {
	    
	      if (resultSet != null) {
	        resultSet.close();
	      }

	      if (statement != null) {
	        statement.close();
	      }
	      if(connect!=null){
	    	  connect.close();
	      }
  }
	  
}